﻿
CREATE proc [dbo].[CreateCalculationFile]
	@FilePath	nvarchar(400),
	@FileName	nvarchar(400), -- incl. full path,
	@SourceFile nvarchar(400) = null,
	@CopyState	bit = 0 output
--WITH EXECUTE AS OWNER
as
begin
	set nocount on;

	
	
	declare @vorlage	nvarchar(400) = '\\TP-S01\server\Vorlagen\Ka\Ka15.xlsm',
			@command	nvarchar(4000) = 'COPY "',
			@rc int,
			@errorMessage nvarchar(400);

	if left(@FilePath,2) != '\\' or right(@FilePath, 1) != '\'
	begin
		raiserror('Angabe Dateipfad ist fehlerhaft',16,1);
		return 1;
	end

	if @SourceFile is not null
	begin
		set @command += @SourceFile + '" "' + @FilePath + @FileName + '"';
	end else
	begin
		set @command += @vorlage + '" "' + @FilePath + @FileName + '"';
	end

	set @CopyState = 0;

	begin try
		--print @command;
		exec @rc = dbo.CheckDirectory
			@chkdirectory = @FilePath, 
			@Create = 1

		if dbo.fnFileExists(@FilePath + @FileName) = 0
		begin
			EXEC master.dbo.xp_cmdshell @command;
			set @CopyState = 1;
		end
		
		
	end try
	begin catch
		set @errorMessage = ERROR_MESSAGE();
		raiserror(@errorMessage, 16,1)
	end catch
	
	return 0;
end
GO
GRANT TAKE OWNERSHIP
    ON OBJECT::[dbo].[CreateCalculationFile] TO [TpOfficeUser]
    WITH GRANT OPTION
    AS [dbo];


GO
GRANT EXECUTE
    ON OBJECT::[dbo].[CreateCalculationFile] TO [TpOfficeUser]
    WITH GRANT OPTION
    AS [dbo];

